package school.repository;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;

import school.domain.Course;

import com.ibm.db2.jcc.a.SqlException;

public class JdbcCourseDao implements CourseDao {

	@Override
	public ArrayList<Course> getByCustomerId(int customerId) throws Exception {
		Connection con = JdbcUtil.connect();
		ArrayList<Course> courses = new ArrayList<Course>();

		String sql = "CALL Get_Courses(?)";
		CallableStatement prepCall = con.prepareCall(sql);

		prepCall.setInt(1, customerId);

		try {
			ResultSet rs = prepCall.executeQuery();
			
			while(rs.next()) {
				Course course = new Course();
				course.setCustomerId(customerId);
				course.setGroupId(rs.getInt(1));
				course.setGroupName(rs.getString(2));
				course.setStaffId(rs.getInt(3));
				course.setCourseStatus(rs.getInt(4) != 0);
				course.setId(rs.getInt(5));
				course.setStaffName(rs.getString(6));
				course.setCategory(rs.getString(7));
				
				courses.add(course);
			}
		} catch (Exception e) {
			throw e;
		} finally {
			JdbcUtil.disconnect();
		}

		return courses;
	}

	@Override
	public void dropOutFromCourse(int courseId, int customerId) throws Exception {
		Connection con = JdbcUtil.connect();

		String sql = "CALL Drop_Out(?,?)";
		CallableStatement prepCall = con.prepareCall(sql);

		prepCall.setInt(1, courseId);	
		prepCall.setInt(2, customerId);
		
		try {
			prepCall.execute();
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			JdbcUtil.disconnect();
		}
	}

	@Override
	public void enroll(int groupId, int customerId, int staffId, int categoryCode) throws Exception {
		Connection con = JdbcUtil.connect();
		String sql = " ";
		
		if(groupId != 0) {
			sql = "CALL Enroll_to_Group(?,?,?)";
		}
		else if(categoryCode != 0) {
			sql = "CALL Enroll_to_Ride(?,?,?)";
		}
		
		CallableStatement prepCall = con.prepareCall(sql);
	
		if(groupId != 0) {
			prepCall.setInt(1, groupId);	
			prepCall.setInt(2, customerId);
			prepCall.setInt(3, staffId);
		}
		else if(categoryCode != 0) {		
			prepCall.setInt(1, customerId);
			prepCall.setInt(2, staffId);		
			prepCall.setInt(2, categoryCode);	
		}
		
		try {
			prepCall.execute();
		} catch (SqlException e) {
			e.printStackTrace();
			throw e;
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			JdbcUtil.disconnect();
		}
	}

	@Override
	public ArrayList<String> getCategories() throws Exception {
		ArrayList<String> categories = new ArrayList<String>();
		
		Connection con = JdbcUtil.connect();
		String sql = "CALL Get_Categories";		
		
		CallableStatement prepCall = con.prepareCall(sql);
		
		try {
			ResultSet rs = prepCall.executeQuery();
			
			while(rs.next()) {
				String category = new String();
				category = rs.getString(1);
				categories.add(category);
			}
				
		} catch (SqlException e) {
			e.printStackTrace();
			throw e;
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			JdbcUtil.disconnect();
		}
		
		return categories;
	}

	@Override
	public ArrayList<Course> getByCustomerIdOrderByStaffName(int customerId) throws Exception {
		Connection con = JdbcUtil.connect();
		ArrayList<Course> courses = new ArrayList<Course>();

		String sql = "CALL Get_Courses_Order_By_Staff_Name(?)";
		CallableStatement prepCall = con.prepareCall(sql);

		prepCall.setInt(1, customerId);

		try {
			ResultSet rs = prepCall.executeQuery();
			
			while(rs.next()) {
				Course course = new Course();
				course.setCustomerId(customerId);
				course.setGroupId(rs.getInt(1));
				course.setGroupName(rs.getString(2));
				course.setStaffId(rs.getInt(3));
				course.setCourseStatus(rs.getInt(4) != 0);
				course.setId(rs.getInt(5));
				course.setStaffName(rs.getString(6));
				course.setCategory(rs.getString(7));
				
				courses.add(course);
			}
		} catch (Exception e) {
			throw e;
		} finally {
			JdbcUtil.disconnect();
		}

		return courses;
	}
}
